import sqlite3


class MySqlite:
    """
    sqlite3数据库 操作工具类
    database: 数据库文件路径
    """

    def __init__(self, database):
        self._conn = sqlite3.connect(database)

    def _dict_factory(self, cursor, row):
        d = {}
        for idx, colume in enumerate(cursor.description):
            d[colume[0]] = row[idx]
        return d

    def execute(self, sql, args=[], result_dict=True, commit=True) -> list:
        """
        执行数据库的工具方法
        :param sql: sql语句
        :param args: sql参数
        :param result_dict: 操作结果是否用dict返回
        :param commit: 是否提交事务
        :return: list列表
        """
        try:
            if result_dict:
                self._conn.row_factory = self._dict_factory
            else:
                self._conn.row_factory = None
            _cursor = self._conn.cursor()
            _cursor.execute(sql, args)

            if commit:
                self._conn.commit()

            data = _cursor.fetchall()
            _cursor.close()
            return data
        except Exception as e:
            raise e

    def add(self, tbl, data) ->bool:
        """
        添加关键词
        :param tbl: 表名
        :param keys: 值
        :return:
        """
        sql = "INSERT INTO {}('content', 'reason', 'type', 'note') VALUES (?,?,?,?)".format(tbl)
        try:
            self._conn.cursor().executemany(sql, data)
            self._conn.commit()
            return True
            # print('插入多条数据成功')
        except Exception as e:
            self._conn.rollback()
            return False
            # print('插入多条数据失败', e)
        finally:
            self._conn.cursor().close()
            self._conn.close()

    def delete(self, tbl, keys):
        """
        删除关键字
        :param tbl: 表名
        :param keys: 值
        :return:
        """
        sql = "DELETE FROM {} WHERE keys='{}'".format(tbl, keys)
        self.execute(sql)

    def query(self, tbl, keys=None) -> list:
        """
        查询关键字
        :param tbl: 表名
        :param keys: 值
        :return:
        """
        if keys is None:
            sql = "SELECT * FROM {}".format(tbl)
        else:
            sql = "SELECT * FROM {} WHERE keys = '{}'".format(tbl, keys)
        data = self.execute(sql)
        return data
